﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using Model;
using DB;

namespace DA
{
    public class StudentService
    {
        string sql = string.Empty;

        //根据用户名得到密码
        public string GetPasswardByStudnetID(string studentID)
        {
            string pwd = string.Empty;
            sql = "select Passward from Student where StudentID=" + "'" + studentID + "'";
            SqlDataReader dr = DB.DBHelper.Exc(sql);
            if (dr.Read())
            {
                pwd = Convert.ToString(dr["Passward"]);
            }
            return pwd;
        }

        //根据姓名得到用户名
        public string GetStudentIDByStudentName(string studentName)
        {
            string studentID = string.Empty;
            sql = "select StudentID from Student where StudentName=" +"'"+studentName+"'";
            SqlDataReader dr = DB.DBHelper.Exc(sql);
            if (dr.Read())
            {
                studentID = Convert.ToString(dr["StudentID"]);
            }
            return studentID;
        }
        
        //修改密码
        public int ModifyPassward(Student student)
        {
            int id = 0;
            StringBuilder updatePassward = new StringBuilder();
            updatePassward.Append("upadte Student set Passward='"+student.Passward+"' where StudentID="+student.StudentID);
            sql = updatePassward.ToString();
            id = DB.DBHelper.ExecuteCommand(sql);
            return id;
        }

        //根据学号得到学生实体
        public Student GetStudent(string studentID) 
        {
            Student student = new Student();
            sql = "select * from Student where StudentID='"+studentID.ToString()+"'";
            SqlDataReader dr = DB.DBHelper.Exc(sql);
            if (dr.Read())
            {
                //student.StudentID = Convert.ToString(dr["StudentID"]);
                student.StudentName = Convert.ToString(dr["StudentName"]);
               
                student.Sex = Convert.ToString(dr["Sex"]);
                student.Birstday = Convert.ToDateTime(dr["Birstday"]);
                student.Nation = Convert.ToString(dr["Nation"]);
                student.PoliticalStatus = Convert.ToString(dr["PoliticalStatus"]);
                student.Profession = Convert.ToString(dr["Profession"]);
                student.EducationExperience = Convert.ToString(dr["EducationExperience"]);
            }
            return student;
        }

        //得到学生信息
        public IList<Student> GetStudent()
        {
            IList<Student> GetStudent = new List<Student>();
            sql = "select * from Student";
            SqlDataReader dr = DB.DBHelper.Exc(sql);
            while (dr.Read())
            {
                Student student = new Student();
                student.StudentID = Convert.ToString(dr["StudentID"]);
                student.StudentName = Convert.ToString(dr["StudentName"]);
                student.Passward = Convert.ToString(dr["Passward"]);
                student.Sex = Convert.ToString(dr["Sex"]);
                student.Birstday = Convert.ToDateTime(dr["Birstday"]);
                student.Nation = Convert.ToString(dr["Nation"]);
                student.PoliticalStatus = Convert.ToString(dr["PoliticalStatus"]);
                student.Profession = Convert.ToString(dr["Profession"]);
                student.EducationExperience = Convert.ToString(dr["EducationExperience"]);
                GetStudent.Add(student);
            }
            return GetStudent;
        }

        //根据学号得到学生信息
        public IList<Student> GetStudentByStudentID(string studentID)
        {
            IList<Student> students = new List<Student>();
            sql = "select * from Student where StudentID='" + studentID.ToString()+"'"; 
            using(SqlDataReader dr = DB.DBHelper.Exc(sql))
            {

                while (dr.Read())
                {
                    Student student = new Student();
                    student.StudentID = Convert.ToString(dr["StudentID"]);
                    student.StudentName = Convert.ToString(dr["StudentName"]);
                    student.Passward = Convert.ToString(dr["Passward"]);
                    student.Sex = Convert.ToString(dr["Sex"]);
                    student.Profession = Convert.ToString(dr["Profession"]);
                    student.Birstday = Convert.ToDateTime(dr["BirstDay"]);
                    student.Nation = Convert.ToString(dr["Nation"]);
                    student.PoliticalStatus = Convert.ToString(dr["PoliticalStatus"]);
                    student.EducationExperience = Convert.ToString(dr["EducationExperience"]);
                    students.Add(student);
                }
            }
            return students;
        }

        //删除学生
        public void DeleteStudent(string studentID)
        {
            sql = "delete from Student where StudentID=" + Convert.ToString(studentID);
            DB.DBHelper.ExecuteCommand(sql);
        }

        //增加学生信息
        public int AddStudent(Student student)
        {
            int id = 0;
            StringBuilder addStudent = new StringBuilder();
            addStudent.Append( "insert into Student(StudentID,StudentName,Passward,Sex,Nation,PoliticalStatus,Birstday,Profession,EducationExperience)values('" + student.StudentID+ "','"
                + student.StudentName + "','"+student.Passward+"','"+student.Sex+"','"+student.Nation+"','"+student.PoliticalStatus+"','"
                +student.Birstday+"','"+student.Profession+"','"+student.EducationExperience+"')");
            sql = addStudent.ToString();
            id = DB.DBHelper.ExecuteCommand(sql);
            return id;
        }

        //编辑学生信息
        public int  ModifyStudent(Student student)
        {
            int id = 0;
            StringBuilder updateStudent = new StringBuilder();
            updateStudent.Append("update Student set StudentName='" + student.StudentName + "',Passward='" + student.Passward + "',Sex='" + student.Sex + "',Nation='" + student.Nation
                + "',PoliticalStatus='" + student.PoliticalStatus + "',Birstday='" + student.Birstday + "',Profession='" + student.Profession
                + "',EducationExperience='" + student.EducationExperience + "'where StudentID=" + student.StudentID);
            sql = updateStudent.ToString();
            id=DB.DBHelper.ExecuteCommand(sql);
            return id;
        }

    }
}
